﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.IO;

namespace NanoERP.Common
{
    /// <summary>
    /// 数据导出操作类
    /// </summary>
    public class ExcelUtil
    {
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="source">数据源</param>
        /// <param name="fileName">保存的文件名</param>
        /// <param name="errorInfo">操作出错信息</param>
        public static void ExportToExcel(DataGridView source, string fileName, out string errorInfo)
        {
            List<DataGridViewRow> rowList = source.Rows.OfType<DataGridViewRow>().ToList();
            List<DataGridViewColumn> columnList = source.Columns.OfType<DataGridViewColumn>().ToList();

            DataGridViewToExcel(rowList, columnList, fileName, out errorInfo);
        }

        /// <summary>
        /// DataGridViewToExcel
        /// </summary>
        /// <param name="rowList">要导出的数据行集合</param>
        /// <param name="columnList">要导出的数据列集合</param>
        /// <param name="fileName">保存的文件名</param>
        /// <param name="errorInfo">操作出错信息</param>
        static void DataGridViewToExcel(List<DataGridViewRow> rowList, List<DataGridViewColumn> columnList, string fileName, out string errorInfo)
        {
            errorInfo = null;
            string filePath = string.Empty;
            if (!JudgeSoruce(rowList.Count, columnList.Count, fileName, out filePath))
                return;
            try
            {
                object Missing = System.Reflection.Missing.Value;
                object excel = null;
                object books = null;
                object book = null;
                object sheets = null;
                object sheet = null;
                object cells = null;
                object[] parameters = null;
                try
                {
                    excel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
                }
                catch
                {
                    throw new Exception("没有安装Microsoft Office Excel。");
                }
                try
                {
                    const int xlMinimized = -4140;

                    parameters = new object[1] { false };
                    excel.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, excel, parameters);

                    parameters = new object[1] { xlMinimized };
                    excel.GetType().InvokeMember("WindowState", BindingFlags.SetProperty, null, excel, parameters);

                    parameters = new object[1] { false };
                    excel.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, excel, parameters);

                    books = excel.GetType().InvokeMember("WorkBooks", BindingFlags.GetProperty, null, excel, null);

                    book = books.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, books, null);

                    sheets = book.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, book, null);
                    parameters = new object[1] { 1 };
                    sheet = sheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, sheets, parameters);

                    //写入表头信息
                    for (int i = 0; i < columnList.Count; i++)
                    {
                        parameters = new Object[2] { 1, i + 1 };
                        cells = sheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, sheet, parameters);
                        //向指定单元格填写内容值
                        parameters = new Object[1] { columnList[i].HeaderText };
                        cells.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cells, parameters);
                    }
                    //写入表中数据内容
                    for (int r = 0; r < rowList.Count; r++)
                    {
                        for (int c = 0; c < columnList.Count; c++)
                        {
                            parameters = new Object[2] { r + 2, c + 1 };
                            cells = sheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, sheet, parameters);

                            var colName = columnList[c].HeaderText;
                            //向指定单元格填写内容值
                            parameters = new Object[1] { colName.Equals("序号") ? r + 1 : rowList[r].Cells[c].Value };
                            cells.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cells, parameters);
                        }
                    }
                }
                catch (Exception ex)
                {
                    errorInfo = ex.Message;
                    return;
                }
                finally
                {
                    //保存文件并退出
                    parameters = new Object[1] { filePath };
                    book.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, book, parameters);
                    ReleaseComObj(sheet);
                    if (book != null)
                    {
                        book.GetType().InvokeMember("Close", System.Reflection.BindingFlags.InvokeMethod, null, book, null);
                        ReleaseComObj(book);
                    }
                    if (books != null)
                    {
                        books.GetType().InvokeMember("Close", System.Reflection.BindingFlags.InvokeMethod, null, books, null);
                        ReleaseComObj(books);
                    }
                    if (excel != null)
                    {
                        excel.GetType().InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, excel, null);
                        ReleaseComObj(excel);
                    }

                    System.GC.Collect();
                }
            }
            catch (Exception ex)
            {
                errorInfo = ex.Message;
                return;
            }
        }

        /// <summary>
        /// 判断数据源是否符合
        /// </summary>
        /// <param name="rowsCount">数据的行数</param>
        /// <param name="colsCount">数据的列数</param>
        /// <param name="filePath">输出路径</param>
        /// <returns></returns>
        static bool JudgeSoruce(int rowsCount, int colsCount, string fileName, out string filePath)
        {
            filePath = string.Empty;
            SaveFileDialog f = new SaveFileDialog();
            f.DefaultExt = "xlsx ";
            f.FileName = fileName;
            f.Filter = "EXCEL文件(*.XLSX)|*.xlsx|EXCEL文件(*.XLS)|*.xls";
            f.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            if (f.ShowDialog() == DialogResult.Cancel)
                return false;
            filePath = f.FileName;
            if (string.IsNullOrEmpty(filePath.Trim()))
                return false;

            //行数必须大于0   
            if (rowsCount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }

            //列数必须大于0   
            if (colsCount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }

            //行数不可以大于65536   
            if (rowsCount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }

            //列数不可以大于255   
            if (colsCount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }

            try
            {
                FileInfo fi = new FileInfo(filePath);
                if (fi.Exists) fi.Delete();
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            return true;
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="objCom">释放对象</param>
        static void ReleaseComObj(object objCom)
        {
            if (objCom != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom);
                objCom = null;
            }
        }

    }
}
